package indi.wshape1.takeawaydeliveryinfo.dao.impl;

import indi.wshape1.takeawaydeliveryinfo.dao.TakeOutDAO;
import indi.wshape1.takeawaydeliveryinfo.domain.TakeOut;

import java.sql.SQLException;
import java.util.List;

/**
 * @author Wshape1
 * @create 2022-11-16 16:08
 */

public class TakeOutDAOImpl extends BaseDAOImpl<TakeOut> implements TakeOutDAO {
    @Override
    public List<TakeOut> getListByGroupAndBlockByPage(String group, String block, int page) {
        return executeQuery("SELECT `id`, `name`, `phone`, `time`, `status`, `group`, `block`, `sender_phone`, `detail` " +
                        "FROM `takeout_info` WHERE `group` = ? AND `block` = ? LIMIT ?, ?",
                group,
                block,
                (page - 1) * countPerPage,
                countPerPage
        );
    }

    @Override
    public List<TakeOut> getListByGroupAndBlockByKeywordAndPage(String group, String block, String keyword, int page) {
        return executeQuery("call getTakeOutListByGroupAndBlockByKeywordAndPage('" + group + "', '" + block + "', '%" +
                keyword +
                "%', " + (page - 1) * countPerPage + ", " + countPerPage + ")"
        );
    }

    @Override
    public int getPageCountByGroupAndBlock(String group, String block, String keyword) {
        String key = "%" + keyword + "%";
        return ((Long) executeComplexQuery("SELECT COUNT(*) FROM `takeout_info` WHERE (`group` = ? AND `block` = ?) " +
                        "AND (`name` LIKE ? OR `phone` LIKE ? OR `group` LIKE ? OR `block` LIKE ? OR `sender_phone` LIKE ?)",
                group,
                block,
                key,
                key,
                key,
                key,
                key
        )[0]).intValue();
    }

    @Override
    public List<TakeOut> getListByBlockByPage(String block, int page) {
        return executeQuery("SELECT `id`, `name`, `phone`, `time`, `status`, `group`, `block`, `sender_phone`, `detail` " +
                        "FROM `takeout_info` WHERE `block` = ? LIMIT ?, ?",
                block,
                (page - 1) * countPerPage,
                countPerPage
        );
    }

    @Override
    public List<TakeOut> getListByBlockByKeywordAndPage(String block, String keyword, int page) {
        return executeQuery("call getTakeOutListByBlockByKeywordAndPage('" + block + "', '%" +
                keyword +
                "%', " + (page - 1) * countPerPage + ", " + countPerPage + ")"
        );
    }

    @Override
    public int getPageCountByBlock(String block, String keyword) {
        String key = "%" + keyword + "%";
        return ((Long) executeComplexQuery("SELECT COUNT(*) FROM `takeout_info` WHERE (`block` = ?) " +
                        "AND (`name` LIKE ? OR `phone` LIKE ? OR `group` LIKE ? OR `block` LIKE ? OR `sender_phone` LIKE ?)",
                block,
                key,
                key,
                key,
                key,
                key
        )[0]).intValue();
    }

    @Override
    public List<TakeOut> getListBySenderPhoneByPage(String se_phone, int page) {
        return executeQuery("SELECT `id`, `name`, `phone`, `time`, `status`, `group`, `block`, `sender_phone`, `detail` " +
                        "FROM `takeout_info` WHERE `sender_phone` = ? LIMIT ?, ?",
                se_phone,
                (page - 1) * countPerPage,
                countPerPage
        );
    }

    @Override
    public List<TakeOut> getListBySenderPhoneByKeywordAndPage(String se_phone, String keyword, int page) {
        String key = "%" + keyword + "%";
        return executeQuery("SELECT `id`, `name`, `phone`, `time`, `status`, `group`, `block`, `sender_phone`, `detail` " +
                        "FROM `takeout_info` WHERE (`sender_phone` = ?) AND " +
                        "(`name` LIKE ? OR `phone` LIKE ? OR `group` LIKE ? OR `block` LIKE ? OR `sender_phone` LIKE ?) " +
                        "LIMIT ?, ?",
                se_phone,
                key,
                key,
                key,
                key,
                key,
                (page - 1) * countPerPage,
                countPerPage
        );
    }

    @Override
    public int getPageCountBySenderPhone(String se_phone, String keyword) {
        String key = "%" + keyword + "%";
        return ((Long) executeComplexQuery("SELECT COUNT(*) FROM `takeout_info` WHERE (`sender_phone` = ?) " +
                        "AND (`name` LIKE ? OR `phone` LIKE ? OR `group` LIKE ? OR `block` LIKE ? OR `sender_phone` LIKE ?)",
                se_phone,
                key,
                key,
                key,
                key,
                key
        )[0]).intValue();
    }

    @Override
    public List<TakeOut> getListByPhonePage(String phone, int page) {
        return executeQuery("SELECT `id`, `name`, `phone`, `time`, `status`, `group`, `block`, `sender_phone`, `detail` " +
                        "FROM `takeout_info` WHERE `phone` = ? LIMIT ?, ?",
                phone,
                (page - 1) * countPerPage,
                countPerPage
        );
    }

    @Override
    public int getPageCountByPhone(String phone) {
        return ((Long) executeComplexQuery("SELECT COUNT(*) FROM `takeout_info` WHERE `phone` = ?",
                phone
        )[0]).intValue();
    }

    @Override
    public void updateInfo(TakeOut takeOut) throws SQLException {
        executeUpdate("UPDATE `takeout_info` SET `name` = ?, `phone` = ?, `time` = ?, `status` = ?," +
                        " `group` = ?, `block` = ?, `sender_phone` = ?, `detail` = ? WHERE `id` = ? LIMIT 1",
                takeOut.getName(),
                takeOut.getPhone(),
                takeOut.getTimeToString(),
                takeOut.getStatus().toString(),
                takeOut.getGroup(),
                takeOut.getBlock(),
                takeOut.getSender_phone(),
                takeOut.getDetail(),
                takeOut.getId()
        );
    }

    @Override
    public void deleteInfo(int id) throws SQLException {
        executeUpdate("DELETE FROM `takeout_info` WHERE `id` = ?", id);
    }

    @Override
    public void addInfo(TakeOut takeOut) {
        try {
            executeUpdate("INSERT INTO `takeout_info`(`name`, `phone`, `time`, `status`, `group`, `block`, `sender_phone`, `detail`)" +
                            " VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                    takeOut.getName(),
                    takeOut.getPhone(),
                    takeOut.getTimeToString(),
                    takeOut.getStatus().toString(),
                    takeOut.getGroup(),
                    takeOut.getBlock(),
                    takeOut.getSender_phone(),
                    takeOut.getDetail()
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public TakeOut getById(int id) {
        List<TakeOut> takeOuts = executeQuery("SELECT `id`, `name`, `phone`, `time`, `status`, `group`, `block`, `sender_phone`, `detail` " +
                "FROM `takeout_info` WHERE `id` = ? LIMIT 1", id);
        if (takeOuts.size() > 0)
            return takeOuts.get(0);
        return null;
    }

    @Override
    public void deleteByBlockGroup(String block, String group) throws SQLException {
        executeUpdate("DELETE FROM `takeout_info` WHERE `group` = ? AND `block` = ?", group, block);
    }

    @Override
    public void deleteByBlock(String block) throws SQLException {
        executeUpdate("DELETE FROM `takeout_info` WHERE `block` = ? ", block);
    }

    @Override
    public List<TakeOut> getListBetweenByPage(String start, String end, int page) {
        return executeQuery("SELECT `id`, `name`, `phone`, `time`, `status`, `group`, `block`, `sender_phone`, `detail` " +
                        "FROM `takeout_info` WHERE `time` BETWEEN ? AND ? LIMIT ?, ?",
                start,
                end,
                (page - 1) * countPerPage,
                countPerPage
        );
    }

    @Override
    public List<TakeOut> getListBetweenByKeywordAndPage(String start, String end, String keyword, int page) {
        String key = "%" + keyword + "%";
        return executeQuery("SELECT `id`, `name`, `phone`, `time`, `status`, `group`, `block`, `sender_phone`, `detail` " +
                        "FROM `takeout_info` WHERE (`time` BETWEEN ? AND ?) AND " +
                        "(`name` LIKE ? OR `phone` LIKE ? OR `group` LIKE ? OR `block` LIKE ? OR `sender_phone` LIKE ?) " +
                        "LIMIT ?, ?",
                start,
                end,
                key,
                key,
                key,
                key,
                key,
                (page - 1) * countPerPage,
                countPerPage
        );
    }

    @Override
    public int getPageCountBetween(String start, String end, String keyword) {
        String key = "%" + keyword + "%";
        return ((Long) executeComplexQuery("SELECT COUNT(*) FROM `takeout_info` WHERE (`time` BETWEEN ? AND ?) " +
                        "AND (`name` LIKE ? OR `phone` LIKE ? OR `group` LIKE ? OR `block` LIKE ? OR `sender_phone` LIKE ?)",
                start,
                end,
                key,
                key,
                key,
                key,
                key
        )[0]).intValue();
    }

    @Override
    public List<TakeOut> getListByKeywordAndPage(String keyword, int page) {
        return executeQuery("call getTakeOutListByKeywordAndPage('%" +
                keyword +
                "%', " + (page - 1) * countPerPage + ", " + countPerPage + ")"
        );
    }

    @Override
    public List<TakeOut> getListByPhone(String phone) {
        return executeQuery("SELECT `id`, `name`, `phone`, `time`, `status`, `group`, `block`, `sender_phone`, `detail` " +
                        "FROM `takeout_info` WHERE `phone` = ?",
                phone);
    }

    @Override
    public int getCountBySenderPhone(String phone) {
        return ((Long) executeComplexQuery("SELECT COUNT(*) FROM `takeout_info` WHERE `sender_phone` = ?",
                phone
        )[0]).intValue();
    }

    @Override
    public int getCountByBlock(String block) {
        return ((Long) executeComplexQuery("SELECT COUNT(*) FROM `takeout_info` WHERE `block` = ?",
                block
        )[0]).intValue();
    }

    @Override
    public int getCountByBlockAndGroup(String block, String group) {
        return ((Long) executeComplexQuery("SELECT COUNT(*) FROM `takeout_info` WHERE `block` = ? AND `group` = ?",
                block, group
        )[0]).intValue();
    }

    @Override
    public int getCount() {
        return ((Long) executeComplexQuery("SELECT COUNT(*) FROM `takeout_info`")[0]).intValue();
    }
}
